package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import gov.nhso.prov.acc.beans.Acc6AllocateBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;

@SuppressWarnings("rawtypes")
public class ACC60404 extends Utility{
	public String url = "/apps/acc6/ACC60404A.jsp";

	public ACC60404(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.init(request);
		}else if (job.equals("search")){
			this.search(request);
		}
	}
	
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("lsProvince", this.dataUtil(dao).lsProvince());
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List lsAllocate = new ArrayList();
			List list = new ArrayList();
			StringBuffer sb = new StringBuffer();
			int j = 0;
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String bufferStr = budgetYear;
			String provTxt= "";
			if (provinceId.equals("")) {
				if (user(request).getAreaLevel().equals("2")) {
					provTxt = " IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+user(request).getAreaCode()+"') ";
				} else {
					provTxt = " IS NOT NULL";
				}
			} else {
				provTxt = " = '"+provinceId+"' ";
			}
			int iBudgetYear = Integer.parseInt(budgetYear);
			//int startBudgetYear = 2;
			//int endBudgetYear = 1;
			Acc6AllocateBean aab = new Acc6AllocateBean();
			aab.setAllocateLevel("1");
			lsAllocate = dao.selectList(aab, " AND BUDGET_YEAR <= "+budgetYear+" ORDER BY BUDGET_YEAR DESC , ALLOCATE_CODE ASC ");
			//dao.printMessage();
			for (int i = 0; i<lsAllocate.size() ; i++) {
				Acc6AllocateBean bean = (Acc6AllocateBean)lsAllocate.get(i);
				sb.delete(0, sb.length());
				if (Integer.parseInt(bean.getBudgetYear()) <= 2556) {
					if (iBudgetYear > 2556) {// 2557 + 
						String budgetYear2 = String.valueOf(Integer.parseInt(bean.getBudgetYear()));
						bean.setSumIncomeAmountTypeB("0");
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('T','P') ");
						sb.append("AND ALLOCATE_CODE LIKE '" + budgetYear2.substring(2, 4)+ bean.getAllocateCode().substring(2, 4) + "%' ");
						sb.append("AND TRUNC(EXPENSE_DATE) >=  TO_DATE('01/10/2012','DD.MM.YYYY') ");
						sb.append("AND TRUNC(EXPENSE_DATE) <=  TO_DATE('30/09/2013','DD.MM.YYYY') AND REASON_CODE = '00' ");
						Map map = dao.findDataSql(sb.toString());
						double expenseAmount = Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));

						sb.setLength(0);
						sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('C') ");
						sb.append("AND ALLOCATE_CODE LIKE '" + budgetYear2.substring(2, 4)+ bean.getAllocateCode().substring(2, 4) + "%' ");
						sb.append("AND (TRUNC(CHEQUE_DATE) >= TO_DATE('01/10/2012','DD.MM.YYYY')  ");
						sb.append("AND TRUNC(CHEQUE_DATE) <= TO_DATE('30/09/2013','DD.MM.YYYY')) AND REASON_CODE = '00' ");
						sb.append("AND REASON_CODE = '00' ");
						map = dao.findDataSql(sb.toString());
						expenseAmount += Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
						
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('B','S','F','R','O') ");
						sb.append("AND ALLOCATE_CODE LIKE '" + budgetYear2.substring(2, 4)+ bean.getAllocateCode().substring(2, 4) + "%' ");
						sb.append("AND TRUNC(INCOME_DATE) >=  TO_DATE('01/10/2012','DD.MM.YYYY')  ");
						sb.append("AND TRUNC(INCOME_DATE) <=  TO_DATE('30/09/2013','DD.MM.YYYY') AND REASON_CODE = '00' ");
						map = dao.findDataSql(sb.toString());
						double imcomeAmount = Double.parseDouble(dao.chkData(map.get("INCOME_AMOUNT")));
						double remain = imcomeAmount - expenseAmount;
						bean.setSumIncomeAmountTypeB(String.valueOf(remain));
					} else {
						sb.append("SELECT SUM(INCOME_AMOUNT) AS B ");
						sb.append("FROM ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID "+provTxt+" ");
						if(bean.getAllocateApply().equals("N")){
							if (bean.getAllocateCode().endsWith("00")) {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode().substring(0, bean.getAllocateCode().length()-2)+"%' ");
							} else {
								sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
							}
						} else {
							sb.append("AND ALLOCATE_CODE = '"+bean.getAllocateCode()+"' ");
						}
						sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
						sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
						sb.append("AND REASON_CODE = '00' AND TRANSACTION_TYPE = 'B' ");
						sb.append("GROUP BY TRANSACTION_TYPE ");
						
						list = dao.listDataSql(sb.toString());
						bean.setSumIncomeAmountTypeB("0");
						for (j = 0;j<list.size();j++){
							Map map = (Map) list.get(j);
							bean.setSumIncomeAmountTypeB(String.valueOf(Double.parseDouble(map.get("B").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeB())));
						}
					}
					
				} else {
					/*int iBudgetYearAD = Integer.parseInt(bean.getBudgetYear()) - 543;
					String budgetYear2 = String.valueOf(Integer.parseInt(bean.getBudgetYear()) - 1);
					bean.setSumIncomeAmountTypeB("0");
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('T','P') ");
					sb.append("AND ALLOCATE_CODE LIKE '" + budgetYear2.substring(2, 4)+ bean.getAllocateCode().substring(2, 4) + "%' ");
					sb.append("AND TRUNC(EXPENSE_DATE) >=  TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
					sb.append("AND TRUNC(EXPENSE_DATE) <=  TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY') AND REASON_CODE = '00' ");
					Map map = dao.findDataSql(sb.toString());
					double expenseAmount = Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));

					sb.setLength(0);
					sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('C') ");
					sb.append("AND ALLOCATE_CODE LIKE '" + budgetYear2.substring(2, 4)+ bean.getAllocateCode().substring(2, 4) + "%' ");
					sb.append("AND (TRUNC(CHEQUE_DATE) >= TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
					sb.append("AND TRUNC(CHEQUE_DATE) <= TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY')) ");
					sb.append("AND REASON_CODE = '00' ");
					map = dao.findDataSql(sb.toString());
					expenseAmount += Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
					
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID " + provTxt + " AND TRANSACTION_TYPE IN ('B','S','F','R','O') ");
					sb.append("AND ALLOCATE_CODE LIKE '" + budgetYear2.substring(2, 4)+ bean.getAllocateCode().substring(2, 4) + "%' ");
					sb.append("AND TRUNC(INCOME_DATE) >=  TO_DATE('01/10/" + (iBudgetYearAD - startBudgetYear) + "','DD.MM.YYYY') ");
					sb.append("AND TRUNC(INCOME_DATE) <=  TO_DATE('30/09/" + (iBudgetYearAD - endBudgetYear) + "','DD.MM.YYYY') AND REASON_CODE = '00' ");
					map = dao.findDataSql(sb.toString());
					double imcomeAmount = Double.parseDouble(dao.chkData(map.get("INCOME_AMOUNT")));
					double remain = imcomeAmount - expenseAmount;*/
					bean.setSumIncomeAmountTypeB("0");
				}
				
				sb.delete(0, sb.length());
				sb.append("SELECT SUM(INCOME_AMOUNT) AS S ");
				sb.append("FROM ACC6_TRANSACTION ");
				sb.append("WHERE PROVINCE_ID "+provTxt+" ");
				sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
				sb.append("AND TRUNC(INCOME_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
				sb.append("AND TRUNC(INCOME_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
				sb.append("AND REASON_CODE = '00' AND TRANSACTION_TYPE IN ('S','F','R','O') ");
				sb.append(" GROUP BY TRANSACTION_TYPE ");
				list = dao.listDataSql(sb.toString());
				bean.setSumIncomeAmountTypeS("0");
				for (j = 0;j<list.size();j++){
					Map map = (Map) list.get(j);
					bean.setSumIncomeAmountTypeS(String.valueOf(Double.parseDouble(map.get("S").toString())+ Double.parseDouble(bean.getSumIncomeAmountTypeS())));
				}
				sb.delete(0, sb.length());
				sb.append("SELECT SUM(EXPENSE_AMOUNT) AS X ");
				sb.append("FROM ACC6_TRANSACTION ");
				sb.append("WHERE PROVINCE_ID "+provTxt+" ");
				sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");

				sb.append("AND TRUNC(EXPENSE_DATE) >=  " + this.dateUtil().changeDateToDB(startDate) + " ");
				sb.append("AND TRUNC(EXPENSE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
				//sb.append("AND REASON_CODE = '00' AND TRANSACTION_TYPE IN ('T','A') ");
				sb.append("AND REASON_CODE = '00' AND TRANSACTION_TYPE IN ('T','P') ");
				sb.append("GROUP BY TRANSACTION_TYPE ");
				list = dao.listDataSql(sb.toString());
				bean.setSumExpenseAmount("0");
				for (j = 0;j<list.size();j++){
					Map map = (Map) list.get(j);
					bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
				}
				
				sb.delete(0, sb.length());
				sb.append("SELECT SUM(EXPENSE_AMOUNT) AS X ");
				sb.append("FROM ACC6_TRANSACTION ");
				sb.append("WHERE PROVINCE_ID "+provTxt+" ");
				sb.append("AND ALLOCATE_CODE LIKE '"+bean.getAllocateCode()+"%' ");
				sb.append("AND TRUNC(CHEQUE_DATE) >= " + this.dateUtil().changeDateToDB(startDate) + " ");
				sb.append("AND TRUNC(CHEQUE_DATE) <= " + this.dateUtil().changeDateToDB(endDate) + " ");
				sb.append("AND REASON_CODE = '00' AND TRANSACTION_TYPE = 'C' ");
				sb.append("GROUP BY TRANSACTION_TYPE ");
				list = dao.listDataSql(sb.toString());
				for (j = 0;j<list.size();j++){
					Map map = (Map) list.get(j);
					bean.setSumExpenseAmount(String.valueOf(Double.parseDouble(map.get("X").toString())+ Double.parseDouble(bean.getSumExpenseAmount())));
				}
				bean.setLastBudgetYear("N");
				if ((i+1) < lsAllocate.size()) {
					Acc6AllocateBean bean2 = (Acc6AllocateBean)lsAllocate.get(i+1);
					if (!bufferStr.equals(bean2.getBudgetYear())) {
						bean.setLastBudgetYear("Y");
						bufferStr = bean2.getBudgetYear();
					}
				} else if ((i+1) == lsAllocate.size()) {
					bean.setLastBudgetYear("Y");
				}
				
				if(lsAllocate.size() < 1){
					this.message(request).setMsgCode("I001");
				}
			}

			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("lsAllocate", lsAllocate);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("budgetYear", budgetYear);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
